﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SyncLib;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServer;

namespace AppCommon
{
    public class ADOSyncConfig
    {
        public static string ConnectionString { get; set; }
        public static string Schema
        {
            get { return CommonVariable.SyncConfigSchema; }
        }
        static ADOSyncConfig()
        {
            ConnectionString = CommonVariable.SqlConnectionString;
        }


        public static DataTable GetAllProcedure()
        {
            string sql = "SELECT o.Name,s.Name [Schema],o.Modify_Date ModifyDate " +
                        " FROM sys.objects o inner join sys.schemas s on o.[Schema_id]=s.[Schema_id]" +
                        " where  type in (N'P', N'PC')";
            return SqlHelper.ExecuteDataTable(ConnectionString, CommandType.Text, sql);
        }

        public static DataTable GetAllTables(string where)
        {

            string sql = "Select * from( select o.Name,s.Name [Schema],o.Modify_Date ModifyDate " +
                        "from Sys.Tables o inner join sys.schemas s " +
                        "on o.[Schema_id]=s.[Schema_id]) T " + where;
            return SqlHelper.ExecuteDataTable(ConnectionString, CommandType.Text, sql);
        }
        public static DataTable GetAllTables()
        {
            string sql = "select o.Name,s.Name [Schema],o.Modify_Date ModifyDate " +
                        "from Sys.Tables o inner join sys.schemas s " +
                        "on o.[Schema_id]=s.[Schema_id]";
            return SqlHelper.ExecuteDataTable(ConnectionString, CommandType.Text, sql);
        }
        public static DataTable GetTableColumn(string table)
        {
            string sql = string.Format("SELECT column_name Name, data_type, character_maximum_length, table_name,ordinal_position, is_nullable " +
                         " FROM information_schema.COLUMNS WHERE table_name =N'{0}' " +
                        "ORDER BY ordinal_position", table);
            return SqlHelper.ExecuteDataTable(ConnectionString, CommandType.Text, sql);


        }
        public static bool ExistsTableInScopeEx(Guid scopeId, string tableName)
        {
            int count = Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, "Select Count(*) from [TableOfScope] where TableName='" + tableName + "' AND [ScopeExId]='" + scopeId.ToString() + "'"));
            return count > 0;
        }
        public static bool ExistsTableInScope(Guid scopeId, string tableName)
        {
            int count = Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, "Select Count(*) from [SyncScopeDef] where TableName='" + tableName + "' AND [SyncScopeId]='" + scopeId.ToString() + "'"));
            return count > 0;
        }
        public DataTable GetAllScopeInfoTable()
        {
            string selectAllScopeInfoTable = "select o.Name TableName, s.Name [Schema] " +
                                "from sysobjects o inner join sys.schemas s on o.uid=s.schema_id " +
                                "Where o.Name=N'Scope_info'";
            return SqlHelper.ExecuteDataTable(ConnectionString, CommandType.Text, selectAllScopeInfoTable);
        }
        public DataTable GetScopeInfo(string schema)
        {
            string sqlSelect = string.Format("Select sync_scope_name Name,'{0}' [Schema] From [{0}].[scope_info] i " +
                            " Where Not Exists(Select * from [{1}].ScopeInfoEx e where i.sync_scope_name=e.ScopeName)", schema, Schema);
            return SqlHelper.ExecuteDataTable(ConnectionString, CommandType.Text, sqlSelect);
        }
        public static int GetMaxOrdinal()
        {
            string maxtOrdinal = string.Format("Select ISNULL(Max([Ordinal]),0) FROM [{0}].SyncScope", Schema);
            return (int)SqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, maxtOrdinal);
        }
        public static void CreateExtenScopeInfo()
        {
            string selectAllScopeInfoTable = "select o.Name TableName, s.Name SchemaName " +
                                "from sysobjects o inner join sys.schemas s on o.uid=s.schema_id " +
                                "Where o.Name=N'Scope_info'";
            DataTable tblData = SqlHelper.ExecuteDataTable(ConnectionString, CommandType.Text, selectAllScopeInfoTable);

            //string selectNewScope = "select * from {0}.scope_info where not exists (Select * from SyncScope where ScopeName=Sync_Scope_name)";
            string selectNewScope = "select * from {0}.scope_info where not exists (Select * from ScopeInfoEx where ScopeName=Sync_Scope_name)";
            string insert = "Insert Into SyncScope([ScopeName],[ScopeType],[Ordinal],[IsEnable],[Description],[Schema])" +
                " Values('{0}',{1},{2},{3},'{4}','{5}')";
            string maxtOrdinal = string.Format("Select ISNULL(Max([Ordinal]),0) FROM [{0}].ScopeInfoEx", Schema);
            foreach (DataRow row in tblData.Rows)
            {

                DataTable tblNewScope = SqlHelper.ExecuteDataTable(ConnectionString, CommandType.Text, string.Format(selectNewScope, row["SchemaName"]));
                SyncConfigDS ds = new SyncConfigDS();
                foreach (DataRow newScope in tblNewScope.Rows)
                {
                    int max = Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, maxtOrdinal));
                    max++;
                    SyncConfigDS.ScopeInfoExRow newScopeInfoExRow = ds.ScopeInfoEx.NewScopeInfoExRow();
                    newScopeInfoExRow.ScopeExId = Guid.NewGuid();
                    newScopeInfoExRow.ScopeName = newScope["sync_scope_name"].ToString();
                    newScopeInfoExRow.Schema = row["SchemaName"].ToString();
                    newScopeInfoExRow.ServerAssembly = "SyncLib.dll";
                    newScopeInfoExRow.ServerClass = "SyncLib.Provider.SimpleSync";
                    newScopeInfoExRow.ClientAssembly = "";
                    newScopeInfoExRow.ClientClass = "";
                    newScopeInfoExRow.ScopeVersion = 1;
                    newScopeInfoExRow.SchemaVersion = 1;
                    newScopeInfoExRow.Comment = "";
                    newScopeInfoExRow.Direction = "";
                    newScopeInfoExRow.ScopeType = 1;
                    newScopeInfoExRow.Ordinal = max;
                    newScopeInfoExRow.AssemblyVersion = 1;
                    newScopeInfoExRow.TemplateName = "";

                    ds.ScopeInfoEx.AddScopeInfoExRow(newScopeInfoExRow);

                }
                AppCommon.SyncConfigDSTableAdapters.ScopeInfoExTableAdapter adapter = new SyncConfigDSTableAdapters.ScopeInfoExTableAdapter();
                adapter.Connection.ConnectionString = ADOSyncConfig.ConnectionString;
                adapter.Update(ds);


                using (SqlConnection conn = new SqlConnection(CommonVariable.SqlConnectionString))
                {

                    conn.Open();

                    foreach (SyncConfigDS.ScopeInfoExRow row2 in ds.ScopeInfoEx)
                    {


                        DbSyncScopeDescription scopeDess = SqlSyncDescriptionBuilder.GetDescriptionForScope(row2.ScopeName, "", row2.Schema, conn);
                        SyncConfigDS.TableOfScopeDataTable tblData2 = ds.TableOfScope;
                        int count = 0;
                        foreach (DbSyncTableDescription node in scopeDess.Tables)
                        {
                            if (ADOSyncConfig.ExistsTableInScopeEx(row2.ScopeExId, node.UnquotedLocalName))
                                continue;

                            SyncConfigDS.TableOfScopeRow newRow = tblData2.NewTableOfScopeRow();
                            DbSyncColumnDescription[] lstPry = node.PkColumns.ToArray<DbSyncColumnDescription>();
                            newRow.TableName = node.UnquotedLocalName;
                            newRow.ScopeExId = row2.ScopeExId;
                            string temp = "";
                            if (lstPry.Length > 0)
                                foreach (DbSyncColumnDescription col in lstPry)
                                {
                                    temp = temp + ";" + col.UnquotedName;
                                }
                            if (temp.Length > 0)
                                temp = temp.Substring(1);
                            newRow.TableId = Guid.NewGuid();
                            newRow.PrimaryKey = temp;
                            newRow.PreApplyCommand = "";
                            newRow.SolveConfigCommand = "";
                            newRow.SolveConfigType = "Auto";
                            newRow.AfterApplyCommand = "";
                            tblData2.AddTableOfScopeRow(newRow);
                            count++;
                        }
                    }
                    AppCommon.SyncConfigDSTableAdapters.TableOfScopeTableAdapter adapter2 = new SyncConfigDSTableAdapters.TableOfScopeTableAdapter();
                    adapter2.Connection.ConnectionString = ADOSyncConfig.ConnectionString;
                    adapter2.Update(ds.TableOfScope);
                }



            }
        }
    }
}
